const Service = require('egg').Service
const town = require('../data/town')
const dayjs = require('dayjs')

class HsService extends Service {
  /**
   * 获取居家隔扫码记录
   */
  async getJjglScanCount(townName) {
    const { knex } = this.app
    const { TABLE_SCAN_RECORD, TABLE_JJGL } = this.config.constant

    return knex
      .select(knex.raw([`count(*) AS total`].join(',')))
      .from(function () {
        this.table(TABLE_SCAN_RECORD)
          .join(TABLE_JJGL, `${TABLE_SCAN_RECORD}.card_no`, '=', `${TABLE_JJGL}.cardno`)
          .select(`${TABLE_SCAN_RECORD}.*`, `${TABLE_JJGL}.lktime`)
          .where(`${TABLE_SCAN_RECORD}.areajd`, townName)
          .where(`${TABLE_JJGL}.lktime`, '>=', dayjs().format('YYYY-MM-DD HH:mm:ss')) // 结束隔离时间大于当前 TODO这里要等真实数据验证一下
          .as('hsResult')
      })
      .groupBy(`hsResult.card_no`)
      .then(res => (res.length ? res[0].total : 0))
  }
}

module.exports = HsService
